## Generate-PsSpecVerbSql.ps1
## Generate the SQL to import all approved verbs into Aspen Management DB

## Expected usage:
## 2) .\Convert-VerbCSV.ps1 '.\Verbs and Parameters.csv' | Export-Csv ConvertedVerbs.csv
## 3) Run Generate-PsSpecVerbSql.ps1, and pipe into createverbs.sql
## 4) Execute createverbs.sql on AspenManagement DB

param($path = $(throw "Please specify the location to ConvertedVerbs.csv."))

"USE [AspenManagement]"

foreach($verb in Import-CSV $path)
{
    $statement = @"
IF NOT EXISTS (SELECT * FROM Verb WHERE Name='{1}')
INSERT INTO [Verb]
           ([Type], [Name], [Definition], [Comments], [Obsoletes], [Pair])
     VALUES
           ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')
GO

UPDATE [Verb]
     SET Type='{0}', Definition='{2}', Comments='{3}', Obsoletes='{4}', Pair='{5}'
     WHERE Name='{1}'
GO
"@

    $statement -f $verb.VerbType, $verb.Name, $verb.Definition, $verb.Comments, $verb.Obsoletes, $verb.Pair
}
